<?php

/*
 * Filename: calendar.php
 * Auhor: trungt
 * Created on Jul 24, 2008 5:08:47 PM
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */

class JBSModelCalendar extends JModel {
	var $_data = array();

	function getDataForWeek($what, $year, $month, $week){
		$numberOfDay = date('t',mktime (0,0,0,$month,1,$year));
		$arr = array();
		if($what == 'assets'){
			for($i=1; $i<=$numberOfDay; $i++){
				$arr[$i-1] = $this->getAssetsOfTheDay($year, $month, $i);
			}
		}
		else if($what == 'events'){
			for($i=1; $i<=$numberOfDay; $i++){
				$arr[$i-1] = $this->getEventsOfTheDay($year, $month, $i);
			}
		}
		return $arr;
	}
	function getDataForMonth($what, $year, $month){
		$numberOfDay = date('t',mktime (0,0,0,$month,1,$year));
		$arr = array();
		if($what == 'assets'){
			for($i=1; $i<=$numberOfDay; $i++){
				$arr[$i-1] = $this->getAssetsOfTheDay($year, $month, $i);
			}
		}
		else if($what == 'events'){
			for($i=1; $i<=$numberOfDay; $i++){
				$arr[$i-1] = $this->getEventsOfTheDay($year, $month, $i);
			}
		}

		return $arr;
	}
	/*
	 * Get All Assets in a date
	 * */
	function getAssetsOfTheDay($year, $month, $day) {
		$date = $year . '-' . $month . '-'. $day;
		// Lets load the data if it doesn't already exist
		if (empty ($this->_data)) {
			$sqlPart = 	"SELECT " .
					"	e.id, (recur_type > 0 ) AS recur, " .
//					"	e.subject, " .
					"	e.content, " .
					"	e.color, " .
					"	e.start_date, " .
					"	e.end_date, " .
					"	e.start_time, " .
					"	e.end_time, '". $date . "' AS today, " .
					"	use_master_color, " .
					"	et.color AS master_color, " .
					"	a.name AS subject, " .
					"	a.img, " .
					"	a.description " .
					"FROM " .
					"	#__jbschedule_events e " .
					"	INNER JOIN #__jbschedule_eventtypes et ON e.eventtype_id = et.id " .
					"	INNER JOIN #__jbschedule_events_assets ea ON e.id = ea.event_id " .
					"	INNER JOIN #__jbschedule_assets a ON a.id = ea.asset_id ";

			$query = $sqlPart .	//No Recurring events
					"WHERE  " .
					"	recur_type = 0 AND e.start_date = '". $date . "' AND e.published = '1' " .
					"UNION " .		//Union datasets
					$sqlPart .
					"WHERE  " .
					"	(recur_type = 1 AND " .
					"		( " .
					"			(e.recur_daily_weekday = 1 AND WEEKDAY( '". $date . "') < 6) OR " .	//Ngay lam viec trong tuan tu Mon -> Sat, WEEKDAY tra ve 0:Mon, 1:Tue...6->Sun
					"			(e.recur_daily_weekday = 0 AND MOD(DAY('". $date . "') - DAY(e.start_date), e.recur_daily_day) = 0) " . //Lap n ngay 1 lan
					"		) " .
					"	) " .
					"	AND " .
					"	(e.start_date <=  '". $date . "' AND e.end_date >=  '". $date . "') AND e.published = '1' " .
					"UNION " .		//Union datasets
					//Weekly Recurring
					$sqlPart .
					"WHERE  " .
					"	( " .
					"		(recur_type = 2 AND " .
					"		(MOD(WEEK('". $date . "',1)-WEEK(e.start_date,1),e.recur_weekly_week) = 0) AND " .
							"FIND_IN_SET(WEEKDAY( '". $date . "'),recur_weekly_week_day) > 0) " .
					"	) " .
					"	AND " .
					"	(e.start_date <=  '". $date . "' AND e.end_date >=  '". $date . "')	AND e.published = '1' " .
					"UNION " . 		//Union datasets
					//Monthly Reccuring
					$sqlPart .
					"WHERE  " .
					"( " .
					"	(recur_type = 3 )" .
					"	AND" .
					"	(" .
					"		( " .
					"			(e.recur_monthly_option = '0') AND ". /*Option 1*/
					"			(SUBSTRING_INDEX(e.recur_monthly_month_day,',',1) = DAY('". $date . "') AND MOD(MONTH('". $date . "')-MONTH(e.start_date), SUBSTRING_INDEX(e.recur_monthly_month_day,',',-1)) = 0) " ./*Ngay thu X cua moi N thang ?*/
					"		) " .
					"		OR( " .
					"			(e.recur_monthly_option = '1') " . /*Option 2*/
					"			AND " .
					"			((WEEKDAY( '". $date . "')=SUBSTRING_INDEX(SUBSTRING_INDEX(e.recur_monthly_month_date,',',2),',',-1)  AND " . /*Ngay trong tuan*/
					"			DAY('". $date . "') > (7 * (SUBSTRING_INDEX(e.recur_monthly_month_date,',',1)-1)) AND DAY('". $date . "') <= (7 * SUBSTRING_INDEX(e.recur_monthly_month_date,',',1))) " . /*Ngay thu n trong tuan*/
					"			AND  " .
					"			MOD(MONTH('". $date . "')-MONTH(e.start_date), SUBSTRING_INDEX(e.recur_monthly_month_date,',',-1)) = 0) " ./*n thang 1 lan*/
					"		) " .
					"	) " .
					") " .
					"AND " .
					"(e.start_date <= '". $date . "' AND e.end_date >= '". $date . "') AND e.published = '1' " .
					"UNION " . 		//Union datasets
					//Yearly Reccuring
					$sqlPart .
					"WHERE  " .
					"( " .
					"	(recur_type = 4 ) " .
					"	AND " .
					" " .
					"	( " .
					"		( " .
					"			(e.recur_yearly_option = '0') " . /*Option 1*/
					"			AND " .
					"			( " .
					"				MONTH('". $date . "')-1 = SUBSTRING_INDEX(e.recur_yearly_year_month,',',1)" . /*In JBS Month [0..11] but in MySQL Month[1..12]*/
					"				AND " .
					"				DAY('". $date . "') = SUBSTRING_INDEX(e.recur_yearly_year_month,',',-1) " .
					"			) " .
					"		) " .
					"		OR " .
					"		( " .
					"			(e.recur_yearly_option = '1') " . /*Option 2*/
					"			AND " .
					"			( " .
					" 				(DAY('". $date . "')>(7 * (SUBSTRING_INDEX(e.recur_yearly_year_months,',',1)-1))  AND  DAY('". $date . "')<=(7 * SUBSTRING_INDEX(e.recur_yearly_year_months,',',1))) ". /*Ngay thu n trong tuan*/
					"				AND " .
					"				(WEEKDAY( '". $date . "') = SUBSTRING_INDEX(SUBSTRING_INDEX(e.recur_yearly_year_months,',',2),',',-1)) " . /*Ngay trong tuan tai vi tri thu n (1,2,3,4,5)*/
					"				AND " .
					"				(MONTH('". $date . "')-1 = SUBSTRING_INDEX(e.recur_yearly_year_months,',',-1)) " ./*Vao thang thu n. In JBS Month [0..11] but in MySQL Month[1..12]*/
					"			) " .
					"		) " .
					"	) " .
					") " .
					"AND " .
					"(e.start_date <=  '". $date . "' AND e.end_date >=  '". $date . "') AND e.published = '1'";

			return $this->_getList($query);
		}
		return null;
	}

	/*
	 * Get All Events in a date
	 * */
	function getEventsOfTheDay($year, $month, $day) {
		$date = $year . '-' . $month . '-'. $day;
		// Lets load the data if it doesn't already exist
		if (empty ($this->_data)) {
			$query = "SELECT " .	//No Recurring events
					"	e.id, (recur_type > 0 ) AS recur, e.subject, e.content, e.color, e.start_date, e.end_date, e.start_time, e.end_time, '". $date . "' AS today, use_master_color, et.color AS master_color " .
					"FROM  " .
					"	#__jbschedule_events e INNER JOIN #__jbschedule_eventtypes et ON e.eventtype_id = et.id " .
					"WHERE  " .
					"	recur_type = 0 AND e.start_date = '". $date . "' AND e.published = '1' " .
					"UNION " .		//Union datasets
					"SELECT " .		//Daily Recurring
					"	e.id, (recur_type > 0 ) AS recur, e.subject, e.content, e.color, e.start_date, e.end_date, e.start_time, e.end_time, '". $date . "' AS today, use_master_color, et.color AS master_color " .
					"FROM " .
					"	#__jbschedule_events e INNER JOIN #__jbschedule_eventtypes et ON e.eventtype_id = et.id " .
					"WHERE " .
					"	(recur_type = 1 AND " .
					"		( " .
					"			(e.recur_daily_weekday = 1 AND WEEKDAY( '". $date . "') < 6) OR " .	//Ngay lam viec trong tuan tu Mon -> Sat, WEEKDAY tra ve 0:Mon, 1:Tue...6->Sun
					"			(e.recur_daily_weekday = 0 AND MOD(DAY('". $date . "') - DAY(e.start_date), e.recur_daily_day) = 0) " . //Lap n ngay 1 lan
					"		) " .
					"	) " .
					"	AND " .
					"	(e.start_date <=  '". $date . "' AND e.end_date >=  '". $date . "') AND e.published = '1' " .
					"UNION " .		//Union datasets
					"SELECT " .		//Weekly Recurring
					"	e.id, (recur_type > 0 ) AS recur, e.subject, e.content, e.color, e.start_date, e.end_date, e.start_time, e.end_time, '". $date . "' AS today, use_master_color, et.color AS master_color " .
					"FROM " .
					"	#__jbschedule_events e INNER JOIN #__jbschedule_eventtypes et ON e.eventtype_id = et.id " .
					"WHERE " .
					"	( " .
					"		(recur_type = 2 AND " .
					"		(MOD(WEEK('". $date . "',1)-WEEK(e.start_date,1),e.recur_weekly_week) = 0) AND " .
							"FIND_IN_SET(WEEKDAY( '". $date . "'),recur_weekly_week_day) > 0) " .
					"	) " .
					"	AND " .
					"	(e.start_date <=  '". $date . "' AND e.end_date >=  '". $date . "')	AND e.published = '1' " .
					"UNION " . 		//Union datasets
					"SELECT " .		//Monthly Reccuring
					"e.id, (recur_type > 0 ) AS recur, e.subject, e.content, e.color, e.start_date, e.end_date, e.start_time, e.end_time, '". $date . "' AS today, use_master_color, et.color AS master_color " .
					"FROM " .
					"#__jbschedule_events e INNER JOIN #__jbschedule_eventtypes et ON e.eventtype_id = et.id " .
					"WHERE " .
					"( " .
					"	(recur_type = 3 )" .
					"	AND" .
					"	(" .
					"		( " .
					"			(e.recur_monthly_option = '0') AND ". /*Option 1*/
					"			(SUBSTRING_INDEX(e.recur_monthly_month_day,',',1) = DAY('". $date . "') AND MOD(MONTH('". $date . "')-MONTH(e.start_date), SUBSTRING_INDEX(e.recur_monthly_month_day,',',-1)) = 0) " ./*Ngay thu X cua moi N thang ?*/
					"		) " .
					"		OR( " .
					"			(e.recur_monthly_option = '1') " . /*Option 2*/
					"			AND " .
					"			((WEEKDAY( '". $date . "')=SUBSTRING_INDEX(SUBSTRING_INDEX(e.recur_monthly_month_date,',',2),',',-1)  AND " . /*Ngay trong tuan*/
					"			DAY('". $date . "') > (7 * (SUBSTRING_INDEX(e.recur_monthly_month_date,',',1)-1)) AND DAY('". $date . "') <= (7 * SUBSTRING_INDEX(e.recur_monthly_month_date,',',1))) " . /*Ngay thu n trong tuan*/
					"			AND  " .
					"			MOD(MONTH('". $date . "')-MONTH(e.start_date), SUBSTRING_INDEX(e.recur_monthly_month_date,',',-1)) = 0) " ./*n thang 1 lan*/
					"		) " .
					"	) " .
					") " .
					"AND " .
					"(e.start_date <= '". $date . "' AND e.end_date >= '". $date . "') AND e.published = '1' " .
					"UNION " . 		//Union datasets
					"SELECT " .		//Yearly Reccuring
					"e.id, (recur_type > 0 ) AS recur, e.subject, e.content, e.color, e.start_date, e.end_date, e.start_time, e.end_time, '". $date . "' AS today, use_master_color, et.color AS master_color " .
					"FROM " .
					"#__jbschedule_events e INNER JOIN #__jbschedule_eventtypes et ON e.eventtype_id = et.id " .
					"WHERE " .
					"( " .
					"	(recur_type = 4 ) " .
					"	AND " .
					" " .
					"	( " .
					"		( " .
					"			(e.recur_yearly_option = '0') " . /*Option 1*/
					"			AND " .
					"			( " .
					"				MONTH('". $date . "')-1 = SUBSTRING_INDEX(e.recur_yearly_year_month,',',1)" . /*In JBS Month [0..11] but in MySQL Month[1..12]*/
					"				AND " .
					"				DAY('". $date . "') = SUBSTRING_INDEX(e.recur_yearly_year_month,',',-1) " .
					"			) " .
					"		) " .
					"		OR " .
					"		( " .
					"			(e.recur_yearly_option = '1') " . /*Option 2*/
					"			AND " .
					"			( " .
					" 				(DAY('". $date . "')>(7 * (SUBSTRING_INDEX(e.recur_yearly_year_months,',',1)-1))  AND  DAY('". $date . "')<=(7 * SUBSTRING_INDEX(e.recur_yearly_year_months,',',1))) ". /*Ngay thu n trong tuan*/
					"				AND " .
					"				(WEEKDAY( '". $date . "') = SUBSTRING_INDEX(SUBSTRING_INDEX(e.recur_yearly_year_months,',',2),',',-1)) " . /*Ngay trong tuan tai vi tri thu n (1,2,3,4,5)*/
					"				AND " .
					"				(MONTH('". $date . "')-1 = SUBSTRING_INDEX(e.recur_yearly_year_months,',',-1)) " ./*Vao thang thu n. In JBS Month [0..11] but in MySQL Month[1..12]*/
					"			) " .
					"		) " .
					"	) " .
					") " .
					"AND " .
					"(e.start_date <=  '". $date . "' AND e.end_date >=  '". $date . "') AND e.published = '1'";

			return $this->_getList($query);
		}
		return null;
	}
}
?>
